set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 400;
set hive.exec.max.dynamic.partitions.pernode = 200;
with network_massage as (
    select code             --编号
         , name             --名称
         , network_type     --网点类型
         , virt_code        --虚拟代理区code
         , virt_name        --虚拟代理区name
         , is_virtual_agent --是否是虚拟代理区 1是
         , manage_code      --管理大区名称
         , manage_name      --管理大区名称
         , regional_id      --大区ID
         , regional_desc    --regional_desc
         , agent_code       --代理区编码
         , agent_name       --代理区名称
         , fran_code        --加盟商编码
         , fran_name        --加盟商名称
         , center_code      --中心编码
         , center_name      --中心名称
         , provider_id      --省份ID
         , provider_desc    --省份
         , city_id          --城市ID
         , city_desc        --城市
         , area_id          --区/县id
         , area_desc        --区/县
         , is_enable
         , is_suspend
         , zone_id
         , zone_code
         , zone_name
         , financial_center_code
         , financial_center_desc
         , area_code
         , area_name
    from jms_dim.dim_network_whole_massage
    where (
                  name = '承运商'
                  or (agent_name not like '%BEST%'
                  and agent_name not like '%总部%'
                  and agent_name not like '%测试%')
              )
),
     adjudication_money as (
         select arbitration_id              --仲裁申报单id
              , responsibility_network_code --裁定网点编号
              , total--裁定金额
              , handling_fee
              , adjudicate_opinion
         from (
                  select arbitration_id              --仲裁申报单id
                       , responsibility_network_code --裁定网点编号
                       , total--裁定金额
                       , handling_fee
                       , adjudicate_opinion
                       , row_number()
                          over (partition by arbitration_id, responsibility_network_code order by type desc ) as rn
                  from jms_dwd.dwd_adjudication_base_dt
                  where dt <= '{{ execution_date | cst_ds }}'
                    and dt >= date_add('{{ execution_date | cst_ds }}', -120)
                    and is_delete != 0
                    and arbitration_id is not null
                    and responsibility_network_code is not null
              ) a
         where rn = 1
     ),
     waybill_detail as (
         select waybill_no
              , customer_code
              , customer_name
              , pick_network_code     --寄件网点code
              , pick_network_name     --寄件网点
              , collect_time          -- 寄件时间
              , order_source_code
              , ordersource_name
              , dispatch_network_code --派件code
              , dispatch_network_name --派件name
              , sender_province_id    --寄件省份id
              , sender_province_name  --寄件省份name
              , sender_city_id        --寄件城市id
              , sender_city_name      --寄件城市id
              , goods_type_code       --物品类型code
              , goods_type_name       --物品类型名称
              , goods_name
              , express_type_code
              , express_type_name
         from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
         where dt >= date_add('{{ execution_date | cst_ds }}', -120)
           and dt <= '{{ execution_date | cst_ds }}'
     ),
     arbitration_worn as (--预警仲裁 重复取最早的damage_eliminate一个
         select '仲裁'                                                                 as work_type                     --工单类型
              , waybill_no                                                           as waybill_no                    --运单号
              , code                                                                 as work_order_code               --问题件类型编码
              , create_time                                                          as finish_time                   --结案时间
              , closing_time                                                         as finish_time_remark            --完结时间
              , create_time                                                          as create_time_remark
              , status                                                               as submit_status                 --状态
              , null                                                                 as is_carrier                    --是否承运商
              , null                                                                 as source                        --来源
              , first_resp_network_id                                                as resp_network_id               --责任网点id
              , first_resp_network_code                                              as resp_network_code             --责任网点code
              , first_resp_network_name                                              as resp_network_name             --责任网点name
              , second_resp_network_id                                               as resp_network_id_2             --责任网点id2
              , second_resp_network_code                                             as resp_network_code_2           --责任网点code2
              , second_resp_network_name                                             as resp_network_name_2           --责任网点name2
              , third_resp_network_id                                                as resp_network_id_3             --责任网点id3
              , third_resp_network_code                                              as resp_network_code_3           --责任网点code3
              , third_resp_network_name                                              as resp_network_name_3           --责任网点name3
              , four_resp_network_id                                                 as resp_network_id_4             --责任网点id4
              , four_resp_network_code                                               as resp_network_code_4           --责任网点code4
              , four_resp_network_name                                               as resp_network_name_4           --责任网点name4
              , five_resp_network_id                                                 as resp_network_id_5             --责任网点id5
              , five_resp_network_code                                               as resp_network_code_5           --责任网点code5
              , five_resp_network_name                                               as resp_network_name_5           --责任网点name5
              , first_resp_parent_id                                                 as resp_proxy_id                 --责任代理区id
              , first_resp_parent_code                                               as resp_proxy_code               --责任代理区code
              , first_resp_parent_name                                               as resp_proxy_name               --责任代理区name
              , second_resp_parent_id                                                as resp_proxy_id_2               --责任代理区id2
              , second_resp_parent_code                                              as resp_proxy_code_2             --责任代理区code2
              , second_resp_parent_name                                              as resp_proxy_name_2             --责任代理区name2
              , third_resp_parent_id                                                 as resp_proxy_id_3               --责任代理区id_3
              , third_resp_parent_code                                               as resp_proxy_code_3             --责任代理区code_3
              , third_resp_parent_name                                               as resp_proxy_name_3             --责任代理区name_3
              , four_resp_parent_id                                                  as resp_proxy_id_4               --责任代理区id_4
              , four_resp_parent_code                                                as resp_proxy_code_4             --责任代理区code_4
              , four_resp_parent_name                                                as resp_proxy_name_4             --责任代理区name_4
              , five_resp_parent_id                                                  as resp_proxy_id_5               --责任代理区id_5
              , five_resp_parent_code                                                as resp_proxy_code_5             --责任代理区code_5
              , five_resp_parent_name                                                as resp_proxy_name_5             --责任代理区name_5
              , create_network_id                                                    as create_network_id             --申报网点id
              , create_network_code                                                  as create_network_code           --申报网点code
              , create_network_name                                                  as create_network_name           --申报网点name
              , create_parent_id                                                     as create_parent_id              --申报代理区id
              , create_parent_code                                                   as create_parent_code            --申报代理区code
              , create_parent_name                                                   as create_parent_name            --申报代理区name
              , first_type_id                                                        as problem_type_id               --一级类型id
              , first_type_code                                                      as problem_type_code             --一级类型code
              , first_type                                                           as problem_type_name             --一级类型name
              , second_type_id                                                       as problem_type_subject_id       --二级类型id
              , second_type_code                                                     as problem_type_subject_code     --二级类型code
              , second_type                                                          as problem_type_subject_name     --二级类型name
              , t6.total                                                             as goods_value                   --金额1
              , t7.total                                                             as goods_value2                  --金额2
              , t8.total                                                             as goods_value3                  --金额3
              , t9.total                                                             as goods_value4                  --金额4
              , t10.total                                                            as goods_value5                  --金额5
              , create_time                                                          as arb_create_time               --仲裁申报时间
              , null                                                                 as delay_day                     --	延误天数
              , exception_desc                                                       as status                        --异常说明
              , null                                                                 as arrival_time                  --责任网点到件时间
              , null                                                                 as collect_time                  --揽收时间
              , handling_fee_total                                                   as handling_fee                  --手续费
              , null                                                                 as valid_status                  --生效状态 1:生效 2:失效
              , row_number() over (partition by waybill_no order by create_time asc) as rn
              , 1                                                                    as flag
              , t6.adjudicate_opinion                                                as finish_type                   --完结类型
              , null                                                                 as damage_eliminate
              , null                                                                 as cost_center                   --成本中心
              , id                                                                   as id
              , goods_value                                                          as zcgoods_value                 --仲裁自带的物品价值
              , responsibility_reply_status                                          as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
              , responsibility_confirm_status                                        as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
              , 0                                                                    as overtime_flag                 --超时未处理标识1超时,0不超时
              , 0                                                                    as warn_worn_flag                --破损,预警破损标识1代表破损,0代表预警
              , null                                                                 as high_worn_flag
         from (
                  select *
                  from jms_dwd.dwd_arbitration_base_dt --仲裁
                  where dt <= '{{ execution_date | cst_ds }}'                   --分区是create_time ,清洗的时候用的update_time
                    and dt >= date_add('{{ execution_date | cst_ds }}', -200)   --需要90天结案时间
                    and to_date(create_time) <= '{{ execution_date | cst_ds }}' --结案时间
                    and to_date(create_time) >= date_add('{{ execution_date | cst_ds }}', -180)
                    and ((first_type_code = '02')
                      or (first_type_code = '04' and second_type_code = '0402')
                      or (first_type_code = '05' and second_type_code = '0502')
                      )
                    and status in (1, 3, 4, 5, 8, 9, 11)
                    and first_resp_parent_name not like 'BEST%'
                    and (first_resp_network_name = '承运商' or first_resp_parent_code <> 'Test0002')
                    and first_resp_parent_code <> '3021888'
              ) t1
                  left join adjudication_money t6
                            on t1.id = t6.arbitration_id
                                and nvl(t1.first_resp_network_code,'-999') = t6.responsibility_network_code
                  left join adjudication_money t7
                            on t1.id = t7.arbitration_id
                                and nvl(t1.second_resp_network_code,'-999') = t7.responsibility_network_code
                  left join adjudication_money t8
                            on t1.id = t8.arbitration_id
                                and nvl(t1.third_resp_network_code,'-999') = t8.responsibility_network_code
                  left join adjudication_money t9
                            on t1.id = t9.arbitration_id
                                and nvl(t1.four_resp_network_code,'-999') = t9.responsibility_network_code
                  left join adjudication_money t10
                            on t1.id = t10.arbitration_id
                                and nvl(t1.five_resp_network_code,'-999') = t10.responsibility_network_code
     ),
     problem_express_wo_worn as (--预警质量工单 产品反馈运单无重复,个人做了去重操作取最早
         select '质量工单'                                                as work_type                     --工单类型
              , waybill_no                                            as waybill_no                    --运单号
              , work_order_code                                       as work_order_code               --质量工单编号
              , create_time                                           as finish_time                   --完结时间
              , finish_time                                           as finish_time_remark            --完结时间
              , create_time                                           as create_time_remark            --完结时间
              , work_order_status                                     as submit_status                 --工单状态 1:待处理  2:待审核  3:已审核  4:已驳回  5:已申诉 6:已结案 7:已撤销 8:已出账
              , null                                                  as is_carrier                    --是否承运商
              , null                                                  as source                        --来源
              , resp_network_id                                       as resp_network_id               --责任网点id
              , resp_network_code                                     as resp_network_code             --责任网点code
              , resp_network_name                                     as resp_network_name             --责任网点name
              , null                                                  as resp_network_id_2             --责任网点id2
              , null                                                  as resp_network_code_2           --责任网点code2
              , null                                                  as resp_network_name_2           --责任网点name2
              , null                                                  as resp_network_id_3             --责任网点id3
              , null                                                  as resp_network_code_3           --责任网点code3
              , null                                                  as resp_network_name_3           --责任网点name3
              , null                                                  as resp_network_id_4             --责任网点id4
              , null                                                  as resp_network_code_4           --责任网点code4
              , null                                                  as resp_network_name_4           --责任网点name4
              , null                                                  as resp_network_id_5             --责任网点id5
              , null                                                  as resp_network_code_5           --责任网点code5
              , null                                                  as resp_network_name_5           --责任网点name5
              , resp_proxy_id                                         as resp_proxy_id                 --责任代理区id
              , resp_proxy_code                                       as resp_proxy_code               --责任代理区code
              , resp_proxy_name                                       as resp_proxy_name               --责任代理区name
              , null                                                  as resp_proxy_id_2               --责任代理区id2
              , null                                                  as resp_proxy_code_2             --责任代理区code2
              , null                                                  as resp_proxy_name_2             --责任代理区name2
              , null                                                  as resp_proxy_id_3               --责任代理区id_3
              , null                                                  as resp_proxy_code_3             --责任代理区code_3
              , null                                                  as resp_proxy_name_3             --责任代理区name_3
              , null                                                  as resp_proxy_id_4               --责任代理区id_4
              , null                                                  as resp_proxy_code_4             --责任代理区code_4
              , null                                                  as resp_proxy_name_4             --责任代理区name_4
              , null                                                  as resp_proxy_id_5               --责任代理区id_5
              , null                                                  as resp_proxy_code_5             --责任代理区code_5
              , null                                                  as resp_proxy_name_5             --责任代理区name_5
              , apply_network_id                                      as create_network_id             --申报网点id
              , apply_network_code                                    as create_network_code           --申报网点code
              , apply_network_name                                    as create_network_name           --申报网点name
              , apply_proxy_id                                        as create_parent_id              --申报代理区id
              , apply_proxy_code                                      as create_parent_code            --申报代理区code
              , apply_proxy_name                                      as create_parent_name            --申报代理区name
              , problem_type_id                                       as problem_type_id               --一级类型id
              , problem_type_code                                     as problem_type_code             --一级类型code
              , problem_type_name                                     as problem_type_name             --一级类型name
              , problem_type_subject_id                               as problem_type_subject_id       --二级类型id
              , problem_type_subject_code                             as problem_type_subject_code     --二级类型code
              , problem_type_subject_name                             as problem_type_subject_name     --二级类型name
              , penalty_amount                                        as goods_value                   --金额1
              , null                                                  as goods_value2                  --金额2
              , null                                                  as goods_value3                  --金额3
              , null                                                  as goods_value4                  --金额4
              , null                                                  as goods_value5                  --金额5
              , null                                                  as arb_create_time               --仲裁申报时间
              , null                                                  as delay_day                     --延误天数
              , problem_description                                   as status                        --异常说明
              , null                                                  as arrival_time                  --责任网点到件时间
              , null                                                  as collect_time                  --揽收时间
              , handling_fee                                          as handling_fee                  --手续费
              , null                                                  as valid_status                  --生效状态 1:生效 2:失效
              , row_number()
                 over (partition by waybill_no order by create_time ) as rn
              , 3                                                     as flag
              , finish_type                                           as finish_type                   --完结类型
              , damage_eliminate                                      as damage_eliminate
              , null                                                  as cost_center                   --成本中心
              , id                                                    as id
              , null                                                  as zcgoods_value                 --仲裁自带的物品价值
              , null                                                  as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
              , null                                                  as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
              , 0                                                     as overtime_flag                 --超时未处理标识1超时,0不超时
              , 0                                                     as warn_worn_flag                --破损,预警破损标识1代表破损,0代表预警
              , 0                                                     as high_worn_flag                --高破损客户1代表搞破损
         from (
                  select *
                  from jms_dwd.dwd_sqs_problem_express_wo_base_dt --质量工单
                  where dt <= '{{ execution_date | cst_ds }}'                   --分区是create_time ,清洗的时候用的update_time
                    and dt >= date_add('{{ execution_date | cst_ds }}', -200)   --需要90天完结时间
                    and to_date(create_time) <= '{{ execution_date | cst_ds }}' --完结时间
                    and to_date(create_time) >= date_add('{{ execution_date | cst_ds }}', -180)
                    and problem_type_code = '27'
                    and resp_proxy_name not like 'BEST%'
                    and (resp_network_name = '承运商' or resp_proxy_name not like '%测试%')
                    and resp_proxy_name not like '%总部%'
                    and resp_network_code NOT LIKE 'B%'
                    and work_order_status in (1, 2, 3, 4, 5)
              ) a
     ),
     problem_express_wo as (--质量工单 产品反馈运单无重复,个人做了去重操作取最早
         select '质量工单'                                                as work_type                     --工单类型
              , waybill_no                                            as waybill_no                    --运单号
              , work_order_code                                       as work_order_code               --质量工单编号
              , finish_time                                           as finish_time                   --完结时间
              , finish_time                                           as finish_time_remark            --完结时间
              , create_time                                           as create_time_remark            --完结时间
              , work_order_status                                     as submit_status                 --工单状态 1:待处理  2:待审核  3:已审核  4:已驳回  5:已申诉 6:已结案 7:已撤销 8:已出账
              , null                                                  as is_carrier                    --是否承运商
              , null                                                  as source                        --来源
              , resp_network_id                                       as resp_network_id               --责任网点id
              , resp_network_code                                     as resp_network_code             --责任网点code
              , resp_network_name                                     as resp_network_name             --责任网点name
              , null                                                  as resp_network_id_2             --责任网点id2
              , null                                                  as resp_network_code_2           --责任网点code2
              , null                                                  as resp_network_name_2           --责任网点name2
              , null                                                  as resp_network_id_3             --责任网点id3
              , null                                                  as resp_network_code_3           --责任网点code3
              , null                                                  as resp_network_name_3           --责任网点name3
              , null                                                  as resp_network_id_4             --责任网点id4
              , null                                                  as resp_network_code_4           --责任网点code4
              , null                                                  as resp_network_name_4           --责任网点name4
              , null                                                  as resp_network_id_5             --责任网点id5
              , null                                                  as resp_network_code_5           --责任网点code5
              , null                                                  as resp_network_name_5           --责任网点name5
              , resp_proxy_id                                         as resp_proxy_id                 --责任代理区id
              , resp_proxy_code                                       as resp_proxy_code               --责任代理区code
              , resp_proxy_name                                       as resp_proxy_name               --责任代理区name
              , null                                                  as resp_proxy_id_2               --责任代理区id2
              , null                                                  as resp_proxy_code_2             --责任代理区code2
              , null                                                  as resp_proxy_name_2             --责任代理区name2
              , null                                                  as resp_proxy_id_3               --责任代理区id_3
              , null                                                  as resp_proxy_code_3             --责任代理区code_3
              , null                                                  as resp_proxy_name_3             --责任代理区name_3
              , null                                                  as resp_proxy_id_4               --责任代理区id_4
              , null                                                  as resp_proxy_code_4             --责任代理区code_4
              , null                                                  as resp_proxy_name_4             --责任代理区name_4
              , null                                                  as resp_proxy_id_5               --责任代理区id_5
              , null                                                  as resp_proxy_code_5             --责任代理区code_5
              , null                                                  as resp_proxy_name_5             --责任代理区name_5
              , apply_network_id                                      as create_network_id             --申报网点id
              , apply_network_code                                    as create_network_code           --申报网点code
              , apply_network_name                                    as create_network_name           --申报网点name
              , apply_proxy_id                                        as create_parent_id              --申报代理区id
              , apply_proxy_code                                      as create_parent_code            --申报代理区code
              , apply_proxy_name                                      as create_parent_name            --申报代理区name
              , problem_type_id                                       as problem_type_id               --一级类型id
              , problem_type_code                                     as problem_type_code             --一级类型code
              , problem_type_name                                     as problem_type_name             --一级类型name
              , problem_type_subject_id                               as problem_type_subject_id       --二级类型id
              , problem_type_subject_code                             as problem_type_subject_code     --二级类型code
              , problem_type_subject_name                             as problem_type_subject_name     --二级类型name
              , penalty_amount                                        as goods_value                   --金额1
              , null                                                  as goods_value2                  --金额2
              , null                                                  as goods_value3                  --金额3
              , null                                                  as goods_value4                  --金额4
              , null                                                  as goods_value5                  --金额5
              , null                                                  as arb_create_time               --仲裁申报时间
              , null                                                  as delay_day                     --延误天数
              , problem_description                                   as status                        --异常说明
              , null                                                  as arrival_time                  --责任网点到件时间
              , null                                                  as collect_time                  --揽收时间
              , handling_fee                                          as handling_fee                  --手续费
              , null                                                  as valid_status                  --生效状态 1:生效 2:失效
              , row_number()
                 over (partition by waybill_no order by finish_time ) as rn
              , 3                                                     as flag
              , finish_type                                           as finish_type                   --完结类型
              , damage_eliminate                                      as damage_eliminate
              , null                                                  as cost_center                   --成本中心
              , id                                                    as id
              , null                                                  as zcgoods_value                 --仲裁自带的物品价值
              , null                                                  as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
              , null                                                  as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
              , if(finish_type in (2, 3), 1, 0)                       as overtime_flag                 --超时未处理标识1超时,0不超时
              , case
                    when finish_subdivide_type != 2 or finish_subdivide_type is null then 1
                    when finish_subdivide_type = 2 then 0
                    else 2 end                                        as warn_worn_flag                --破损,预警破损标识1代表破损,0高为破损
              , 0                                                     as high_worn_flag                --高破损客户1代表搞破损
         from (
                  select *
                  from jms_dwd.dwd_sqs_problem_express_wo_base_dt --质量工单
                  where dt <= '{{ execution_date | cst_ds }}'                   --分区是create_time ,清洗的时候用的update_time
                    and dt >= date_add('{{ execution_date | cst_ds }}', -200)   --需要90天完结时间
                    and to_date(finish_time) <= '{{ execution_date | cst_ds }}' --完结时间
                    and to_date(finish_time) >= date_add('{{ execution_date | cst_ds }}', -180)
                    and problem_type_code = '27'
--            and resp_proxy_name not like 'BEST%'
--            and (resp_network_name = '承运商' or resp_proxy_name not like '%测试%')
                    and (resp_network_name = '承运商' or resp_proxy_code <> 'Test0002')
--            and resp_proxy_name not like '%总部%'
                    and resp_proxy_code <> '3021888'                            --剔除责任网点所属代理区是总部市场部（编码3021888）、测试代理区2（编码Test0002）
                    and (finish_type in (2, 3, 6, 11, 13, 15) or hq_handle_type in (1, 2, 3, 5, 8))
--                     AND (finish_subdivide_type != 2
--                       or finish_subdivide_type is null)
                    and resp_network_code NOT LIKE 'B%'
                    and damage_eliminate = 2
              ) a
     ),
     arbitration as (--仲裁 重复取最早的damage_eliminate一个
         select '仲裁'                                                                  as work_type                     --工单类型
              , waybill_no                                                            as waybill_no                    --运单号
              , code                                                                  as work_order_code               --问题件类型编码
              , closing_time                                                          as finish_time                   --结案时间
              , closing_time                                                          as finish_time_remark            --完结时间
              , create_time                                                           as create_time_remark
              , status                                                                as submit_status                 --状态
              , null                                                                  as is_carrier                    --是否承运商
              , null                                                                  as source                        --来源
              , first_resp_network_id                                                 as resp_network_id               --责任网点id
              , first_resp_network_code                                               as resp_network_code             --责任网点code
              , first_resp_network_name                                               as resp_network_name             --责任网点name
              , second_resp_network_id                                                as resp_network_id_2             --责任网点id2
              , second_resp_network_code                                              as resp_network_code_2           --责任网点code2
              , second_resp_network_name                                              as resp_network_name_2           --责任网点name2
              , third_resp_network_id                                                 as resp_network_id_3             --责任网点id3
              , third_resp_network_code                                               as resp_network_code_3           --责任网点code3
              , third_resp_network_name                                               as resp_network_name_3           --责任网点name3
              , four_resp_network_id                                                  as resp_network_id_4             --责任网点id4
              , four_resp_network_code                                                as resp_network_code_4           --责任网点code4
              , four_resp_network_name                                                as resp_network_name_4           --责任网点name4
              , five_resp_network_id                                                  as resp_network_id_5             --责任网点id5
              , five_resp_network_code                                                as resp_network_code_5           --责任网点code5
              , five_resp_network_name                                                as resp_network_name_5           --责任网点name5
              , first_resp_parent_id                                                  as resp_proxy_id                 --责任代理区id
              , first_resp_parent_code                                                as resp_proxy_code               --责任代理区code
              , first_resp_parent_name                                                as resp_proxy_name               --责任代理区name
              , second_resp_parent_id                                                 as resp_proxy_id_2               --责任代理区id2
              , second_resp_parent_code                                               as resp_proxy_code_2             --责任代理区code2
              , second_resp_parent_name                                               as resp_proxy_name_2             --责任代理区name2
              , third_resp_parent_id                                                  as resp_proxy_id_3               --责任代理区id_3
              , third_resp_parent_code                                                as resp_proxy_code_3             --责任代理区code_3
              , third_resp_parent_name                                                as resp_proxy_name_3             --责任代理区name_3
              , four_resp_parent_id                                                   as resp_proxy_id_4               --责任代理区id_4
              , four_resp_parent_code                                                 as resp_proxy_code_4             --责任代理区code_4
              , four_resp_parent_name                                                 as resp_proxy_name_4             --责任代理区name_4
              , five_resp_parent_id                                                   as resp_proxy_id_5               --责任代理区id_5
              , five_resp_parent_code                                                 as resp_proxy_code_5             --责任代理区code_5
              , five_resp_parent_name                                                 as resp_proxy_name_5             --责任代理区name_5
              , create_network_id                                                     as create_network_id             --申报网点id
              , create_network_code                                                   as create_network_code           --申报网点code
              , create_network_name                                                   as create_network_name           --申报网点name
              , create_parent_id                                                      as create_parent_id              --申报代理区id
              , create_parent_code                                                    as create_parent_code            --申报代理区code
              , create_parent_name                                                    as create_parent_name            --申报代理区name
              , first_type_id                                                         as problem_type_id               --一级类型id
              , first_type_code                                                       as problem_type_code             --一级类型code
              , first_type                                                            as problem_type_name             --一级类型name
              , second_type_id                                                        as problem_type_subject_id       --二级类型id
              , second_type_code                                                      as problem_type_subject_code     --二级类型code
              , second_type                                                           as problem_type_subject_name     --二级类型name
              , t6.total                                                              as goods_value                   --金额1
              , t7.total                                                              as goods_value2                  --金额2
              , t8.total                                                              as goods_value3                  --金额3
              , t9.total                                                              as goods_value4                  --金额4
              , t10.total                                                             as goods_value5                  --金额5
              , create_time                                                           as arb_create_time               --仲裁申报时间
              , null                                                                  as delay_day                     --	延误天数
              , exception_desc                                                        as status                        --异常说明
              , null                                                                  as arrival_time                  --责任网点到件时间
              , null                                                                  as collect_time                  --揽收时间
              , handling_fee_total                                                    as handling_fee                  --手续费
              , null                                                                  as valid_status                  --生效状态 1:生效 2:失效
              , row_number() over (partition by waybill_no order by closing_time asc) as rn
              , 1                                                                     as flag
              , t6.adjudicate_opinion                                                 as finish_type                   --完结类型
              , null                                                                  as damage_eliminate
              , null                                                                  as cost_center                   --成本中心
              , id                                                                    as id
              , goods_value                                                           as zcgoods_value                 --仲裁自带的物品价值
              , responsibility_reply_status                                           as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
              , responsibility_confirm_status                                         as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
              , if(responsibility_reply_status = 1 and responsibility_confirm_status = 1, 1,
                   0)                                                                 as overtime_flag                 --超时未处理标识1超时,0不超时
              , 1                                                                     as warn_worn_flag                --破损,预警破损标识1代表破损,0代表预警
              , null                                                                  as high_worn_flag
         from (
                  select *
                       , row_number() over (partition by waybill_no order by problem_flag desc) as problem_rn
                  from (
                           select *, if(first_type_code = '02', 1, 0) as problem_flag
                           from jms_dwd.dwd_arbitration_base_dt --仲裁
                           where dt <= '{{ execution_date | cst_ds }}'                    --分区是create_time ,清洗的时候用的update_time
                             and dt >= date_add('{{ execution_date | cst_ds }}', -200)    --需要90天结案时间
                             and to_date(closing_time) <= '{{ execution_date | cst_ds }}' --结案时间
                             and to_date(closing_time) >= date_add('{{ execution_date | cst_ds }}', -180)
                             -- and first_type_code in ('02', '05', '04')
                             -- and second_type_code in ('0502', '0402')
                             and ((first_type_code = '02')
                               or (first_type_code = '04' and second_type_code = '0402')
                               or (first_type_code = '05' and second_type_code = '0502')
                               )
                             and status = 10
                             and first_resp_parent_name not like 'BEST%'
--                              and (first_resp_network_name = '承运商' or first_resp_parent_name not like '%测试%')
--                              and first_resp_parent_name not like '%总部%'
                             and (first_resp_network_name = '承运商' or first_resp_parent_code <> 'Test0002')
                             and first_resp_parent_code <> '3021888'
                       ) a
              ) t1
                  left join adjudication_money t6
                            on t1.id = t6.arbitration_id
                                and nvl(t1.first_resp_network_code,'-999') = t6.responsibility_network_code
                  left join adjudication_money t7
                            on t1.id = t7.arbitration_id
                                and nvl(t1.second_resp_network_code,'-999') = t7.responsibility_network_code
                  left join adjudication_money t8
                            on t1.id = t8.arbitration_id
                                and nvl(t1.third_resp_network_code,'-999') = t8.responsibility_network_code
                  left join adjudication_money t9
                            on t1.id = t9.arbitration_id
                                and nvl(t1.four_resp_network_code,'-999') = t9.responsibility_network_code
                  left join adjudication_money t10
                            on t1.id = t10.arbitration_id
                                and nvl(t1.five_resp_network_code,'-999') = t10.responsibility_network_code
         where t1.problem_rn = 1
     ),
     arbitration_delete as (
         select waybill_no
         from jms_dwd.dwd_arbitration_base_dt --仲裁
         where dt <= '{{ execution_date | cst_ds }}'                    --分区是create_time ,清洗的时候用的update_time
           and dt >= date_add('{{ execution_date | cst_ds }}', -200)    --需要90天结案时间
           and to_date(closing_time) <= '{{ execution_date | cst_ds }}' --结案时间
           and to_date(closing_time) >= date_add('{{ execution_date | cst_ds }}', -180)
           and ((first_type_code = '03')
             or (first_type_code = '04' and second_type_code = '0403')
             or (first_type_code = '05' and second_type_code = '0503')
             )
           and status = 10
         group by waybill_no
     ),
     claim_detail as (--线上理赔  产品反馈运单无重复,个人做了去重操作 取最早
         select '线上理赔'                                                           as work_type                     --工单类型
              , waybill_no                                                       as waybill_no                    --运单号
              , claim_no                                                         as work_order_code               --理赔编号
              , submit_time                                                      as finish_time                   --提交时间
              , submit_time                                                      as finish_time_remark            --完结时间
              , create_time                                                      as create_time_remark
              , submit_status                                                    as submit_status                 --状态
              , is_carrier                                                       as is_carrier                    --是否承运商
              , source                                                           as source                        --只要1-客服
              , first_duty_network_id                                            as resp_network_id               --责任网点id
              , first_duty_network_code                                          as resp_network_code             --责任网点code
              , first_duty_network_name                                          as resp_network_name             --责任网点name
              , second_duty_network_id                                           as resp_network_id_2             --责任网点id2
              , second_duty_network_code                                         as resp_network_code_2           --责任网点code2
              , second_duty_network_name                                         as resp_network_name_2           --责任网点name2
              , third_duty_network_id                                            as resp_network_id_3             --责任网点id3
              , third_duty_network_code                                          as resp_network_code_3           --责任网点code3
              , third_duty_network_name                                          as resp_network_name_3           --责任网点name3
              , null                                                             as resp_network_id_4             --责任网点id4
              , null                                                             as resp_network_code_4           --责任网点code4
              , null                                                             as resp_network_name_4           --责任网点name4
              , null                                                             as resp_network_id_5             --责任网点id5
              , null                                                             as resp_network_code_5           --责任网点code5
              , null                                                             as resp_network_name_5           --责任网点name5
              , first_duty_proxy_id                                              as resp_proxy_id                 --责任代理区id
              , first_duty_proxy_code                                            as resp_proxy_code               --责任代理区code
              , first_duty_proxy_name                                            as resp_proxy_name               --责任代理区name
              , second_duty_proxy_id                                             as resp_proxy_id_2               --责任代理区id2
              , second_duty_proxy_code                                           as resp_proxy_code_2             --责任代理区code2
              , second_duty_proxy_name                                           as resp_proxy_name_2             --责任代理区name2
              , third_duty_proxy_id                                              as resp_proxy_id_3               --责任代理区id_3
              , third_duty_proxy_code                                            as resp_proxy_code_3             --责任代理区code_3
              , third_duty_proxy_name                                            as resp_proxy_name_3             --责任代理区name_3
              , null                                                             as resp_proxy_id_4               --责任代理区id_4
              , null                                                             as resp_proxy_code_4             --责任代理区code_4
              , null                                                             as resp_proxy_name_4             --责任代理区name_4
              , null                                                             as resp_proxy_id_5               --责任代理区id_5
              , null                                                             as resp_proxy_code_5             --责任代理区code_5
              , null                                                             as resp_proxy_name_5             --责任代理区name_5
              , null                                                             as create_network_id             --申报网点id
              , null                                                             as create_network_code           --申报网点code
              , null                                                             as create_network_name           --申报网点name
              , null                                                             as create_parent_id              --申报代理区id
              , null                                                             as create_parent_code            --申报代理区code
              , null                                                             as create_parent_name            --申报代理区name
              , problem_type                                                     as problem_type_id               --只要 1-遗失
              , problem_type                                                     as problem_type_code             --一级类型code
              , '破损'                                                             as problem_type_name             --一级类型name
              , problem_type                                                     as problem_type_subject_id       --二级类型id
              , problem_type                                                     as problem_type_subject_code     --二级类型code
              , '破损'                                                             as problem_type_subject_name     --二级类型name
              , first_duty_pay_amount                                            as goods_value                   --金额1
              , second_duty_pay_amount                                           as goods_value2                  --金额2
              , third_duty_pay_amount                                            as goods_value3                  --金额3
              , null                                                             as goods_value4                  --金额4
              , null                                                             as goods_value5                  --金额5
              , null                                                             as arb_create_time               --仲裁申报时间
              , null                                                             as delay_day                     --延误天数
              , remark                                                           as status                        --异常说明
              , null                                                             as arrival_time                  --责任网点到件时间
              , null                                                             as collect_time                  --揽收时间
              , null                                                             as handling_fee                  --手续费
              , valid_status                                                     as valid_status                  --生效状态 1:生效 2:失效
              , row_number() over (partition by waybill_no order by submit_time) as rn
              , 2                                                                as flag
              , null                                                             as finish_type                   --完结类型
              , null                                                             as damage_eliminate
              , cost_center                                                      as cost_center                   --成本中心
              , id                                                               as id
              , null                                                             as zcgoods_value                 --仲裁自带的物品价值
              , null                                                             as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
              , null                                                             as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
              , null                                                             as overtime_flag                 --超时未处理标识1超时,0不超时
              , 1                                                                as warn_worn_flag                --破损,预警破损标识1代表破损,0代表预警
              , null                                                             as high_worn_flag                --高破损客户1代表搞破损
         from jms_dwd.dwd_sqs_claim_detail_base_dt --线上理赔
         where dt <= '{{ execution_date | cst_ds }}'                   --分区是create_time ,清洗的时候用的update_time
           and dt >= date_add('{{ execution_date | cst_ds }}', -200)   --需要90天完结时间
           and to_date(submit_time) <= '{{ execution_date | cst_ds }}' --提交时间
           and to_date(submit_time) >= date_add('{{ execution_date | cst_ds }}', -180)
           and problem_type = 2
           and source = 1
           and submit_status = 1
           and valid_status = 1
           and first_duty_proxy_name not like '%BEST%'
--            and (first_duty_network_name = '承运商' or first_duty_proxy_name not like '%测试%')
--            and first_duty_proxy_name not like '%总部%'
           and (first_duty_network_name = '承运商' or first_duty_proxy_code <> 'Test0002')
           and first_duty_proxy_code <> '3021888'
     ),
     union_all_table as (--若运单号重复则优先保留仲裁，其次保留线上理赔，最后保留质量工单数据
         select *
              , case
                    when resp_network_code_5 is not null and resp_network_code_5 <> '' then 5
                    when resp_network_code_4 is not null and resp_network_code_4 <> '' then 4
                    when resp_network_code_3 is not null and resp_network_code_3 <> '' then 3
                    when resp_network_code_2 is not null and resp_network_code_2 <> '' then 2
                    when resp_network_code is not null and resp_network_code <> '' then 1
                    else 0 end as network_cnt --责任网点数
         from (
                  select 1                                                          as computer_flag --仲裁,质量,线上理赔(破损件)
                       , *
                       , dense_rank() over (partition by waybill_no order by flag ) as flag_rm
                  from (
                           select *
                           from problem_express_wo
                           where rn = 1
                             and warn_worn_flag = 1
                           union all
                           select *
                           from arbitration
                           where rn = 1
                           union all
                           select *
                           from claim_detail
                           where rn = 1
                       ) a
                  union all
                  select 2 as computer_flag --质量(高危破损)
                       , *
                       , 1 as flag_rm
                  from problem_express_wo
                  where rn = 1
                    and warn_worn_flag = 0
                  union all
                  select 3 as computer_flag --仲裁(预警),质量(预警和高危破损)
                       , *
                       , 1 as flag_rm
                  from (
                           select *
                           from arbitration_worn
                           union all
                           select *
                           from problem_express_wo_worn
                       )
                  where rn = 1
              ) b
     ),
     result_table1 as (
         select t1.computer_flag
              , t1.work_type                                                   as work_type                 --工单类型
              , t1.waybill_no                                                  as waybill_no                --运单号
              , t1.work_order_code                                             as work_order_code           --理赔编号
              , t1.finish_time                                                 as finish_time               --提交时间
              , t1.submit_status                                               as submit_status             --状态
              , t1.is_carrier                                                  as is_carrier                --是否承运商
              , t1.source                                                      as source                    --只要1-客服
              , t1.resp_network_id                                             as resp_network_id           --责任网点id
              , t1.resp_network_code                                           as resp_network_code         --责任网点code
              , t1.resp_network_name                                           as resp_network_name         --责任网点name
              , t1.resp_network_id_2                                           as resp_network_id_2         --责任网点id2
              , t1.resp_network_code_2                                         as resp_network_code_2       --责任网点code2
              , t1.resp_network_name_2                                         as resp_network_name_2       --责任网点name2
              , t1.resp_network_id_3                                           as resp_network_id_3         --责任网点id3
              , t1.resp_network_code_3                                         as resp_network_code_3       --责任网点code3
              , t1.resp_network_name_3                                         as resp_network_name_3       --责任网点name3
              , t1.resp_network_id_4                                           as resp_network_id_4         --责任网点id4
              , t1.resp_network_code_4                                         as resp_network_code_4       --责任网点code4
              , t1.resp_network_name_4                                         as resp_network_name_4       --责任网点name4
              , t1.resp_network_id_5                                           as resp_network_id_5         --责任网点id5
              , t1.resp_network_code_5                                         as resp_network_code_5       --责任网点code5
              , t1.resp_network_name_5                                         as resp_network_name_5       --责任网点name5
              , t1.resp_proxy_id                                               as resp_proxy_id             --责任代理区id
              , t1.resp_proxy_code                                             as resp_proxy_code           --责任代理区code
              , t1.resp_proxy_name                                             as resp_proxy_name           --责任代理区name
              , t1.resp_proxy_id_2                                             as resp_proxy_id_2           --责任代理区id2
              , t1.resp_proxy_code_2                                           as resp_proxy_code_2         --责任代理区code2
              , t1.resp_proxy_name_2                                           as resp_proxy_name_2         --责任代理区name2
              , t1.resp_proxy_id_3                                             as resp_proxy_id_3           --责任代理区id_3
              , t1.resp_proxy_code_3                                           as resp_proxy_code_3         --责任代理区code_3
              , t1.resp_proxy_name_3                                           as resp_proxy_name_3         --责任代理区name_3
              , t1.resp_proxy_id_4                                             as resp_proxy_id_4           --责任代理区id_4
              , t1.resp_proxy_code_4                                           as resp_proxy_code_4         --责任代理区code_4
              , t1.resp_proxy_name_4                                           as resp_proxy_name_4         --责任代理区name_4
              , t1.resp_proxy_id_5                                             as resp_proxy_id_5           --责任代理区id_5
              , t1.resp_proxy_code_5                                           as resp_proxy_code_5         --责任代理区code_5
              , t1.resp_proxy_name_5                                           as resp_proxy_name_5         --责任代理区name_5
              , t1.create_network_id                                           as create_network_id         --申报网点id
              , t1.create_network_code                                         as create_network_code       --申报网点code
              , t1.create_network_name                                         as create_network_name       --申报网点name
              , t1.create_parent_id                                            as create_parent_id          --申报代理区id
              , t1.create_parent_code                                          as create_parent_code        --申报代理区code
              , t1.create_parent_name                                          as create_parent_name        --申报代理区name
              , t1.problem_type_id                                             as problem_type_id           --只要 1-遗失
              , t1.problem_type_code                                           as problem_type_code         --一级类型code
              , t1.problem_type_name                                           as problem_type_name         --一级类型name
              , t1.problem_type_subject_id                                     as problem_type_subject_id   --二级类型id
              , t1.problem_type_subject_code                                   as problem_type_subject_code --二级类型code
              , t1.problem_type_subject_name                                   as problem_type_subject_name --二级类型name
              , goods_value                                                    as goods_value               --金额1
              , goods_value2                                                   as goods_value2              --金额2
              , goods_value3                                                   as goods_value3              --金额3
              , goods_value4                                                   as goods_value4              --金额4
              , goods_value5                                                   as goods_value5              --金额5
              , t1.arb_create_time                                             as arb_create_time           --仲裁申报时间
              , t1.delay_day                                                   as delay_day                 --延误天数
              , t1.status                                                      as status                    --异常说明
              , t1.arrival_time                                                as arrival_time              --责任网点到件时间
              , t1.collect_time                                                as collect_time
              , t1.handling_fee                                                as handling_fee              --手续费
              , t1.valid_status                                                as valid_status              --生效状态 1:生效 2:失效
              , t1.rn                                                          as rn                        --每种工单的去重row_number()
              , t1.flag                                                        as flag                      --仲裁1,线上理赔2,质量工单3
              , t1.finish_type                                                 as finish_type               --完结类型
              , t1.flag_rm                                                     as rm                        --根据运单分组,flag排序,做dense_rank()
              , t2.network_type                                                as duty_network_type         --责任网点网点类型(基础资料)
              , nvl(t2.virt_code, t2.agent_code)                               as duty_virt_code            --责任网点虚拟代理区code(基础资料)
              , nvl(t2.virt_name, t2.agent_name)                               as duty_virt_name            --责任网点虚拟代理区name(基础资料)
              , t2.manage_code                                                 as duty_manage_code          --责任网点管理大区名称(基础资料)
              , t2.manage_name                                                 as duty_manage_name          --责任网点管理大区名称(基础资料)
              , t2.regional_id                                                 as duty_regional_id          --责任网点大区ID(基础资料)
              , t2.regional_desc                                               as duty_regional_desc        --责任网点大区name(基础资料)
              , if(t2.name = '承运商', t2.financial_center_code, t2.agent_code)   as duty_agent_code           --责任网点代理区编码(基础资料)
              , if(t2.name = '承运商', t2.financial_center_desc, t2.agent_name)   as duty_agent_name           --责任网点代理区名称(基础资料)
              , t2.fran_code                                                   as duty_fran_code            --责任网点加盟商编码(基础资料)
              , t2.fran_name                                                   as duty_fran_name            --责任网点加盟商名称(基础资料)
              , t2.center_code                                                 as duty_center_code          --责任网点中心编码(基础资料)
              , t2.center_name                                                 as duty_center_name          --责任网点中心名称(基础资料)
              , t2.provider_id                                                 as duty_provider_id          --责任网点省份ID(基础资料)
              , t2.provider_desc                                               as duty_provider_desc        --责任网点省份(基础资料)
              , t2.city_id                                                     as duty_city_id              --责任网点城市ID(基础资料)
              , t2.city_desc                                                   as duty_city_desc            --责任网点城市(基础资料)
              , t2.area_id                                                     as duty_area_id              --责任网点区/县id
              , t2.area_desc                                                   as duty_area_desc            --责任网点区/县
              , t2.is_enable                                                   as duty_is_enable            --责任网点状态 0待启用,1启用,2禁用
              , t2.is_suspend                                                  as duty_is_suspend           --责任网点是否暂停网点(1是,2否)
              , t2.zone_id                                                     as transfer_district_id      --责任网点片区id
              , t2.zone_code                                                   as transfer_district_code    --责任网点片区code
              , t2.zone_name                                                   as transfer_district_desc    --责任网点片区desc
              , t2.is_virtual_agent                                            as duty_is_virtual_agent     --是否是虚拟代理区 1是
              , t3.manage_code                                                 as sb_manage_code            --申报网点管理大区名称(基础资料)
              , t3.manage_name                                                 as sb_manage_name            --申报网点管理大区名称(基础资料)
              , t3.regional_id                                                 as sb_regional_id            --申报网点大区ID(基础资料)
              , t3.regional_desc                                               as sb_regional_desc          --申报网点大区name(基础资料)
              , t3.network_type                                                as sb_network_type           --申报网点网点类型(基础资料)
              , if(t1.flag_rm = 1, 1, 0)                                       as is_delete                 --1代表需要的数据未剔除0是不要的
              , t6.manage_code                                                 as duty_manage_code_2        --第二责任网点管理大区名称(基础资料)
              , t6.manage_name                                                 as duty_manage_name_2        --第二责任网点管理大区名称(基础资料)
              , t6.regional_id                                                 as duty_regional_id_2        --第二责任网点大区ID(基础资料)
              , t6.regional_desc                                               as duty_regional_desc_2      --第二责任网点大区name(基础资料)
              , if(t6.name = '承运商', t6.financial_center_code, t6.agent_code)   as duty_agent_code_2         --第二责任网点代理区编码(基础资料)
              , if(t6.name = '承运商', t6.financial_center_desc, t6.agent_name)   as duty_agent_name_2         --第二责任网点代理区编码(基础资料)
              , t6.network_type                                                as duty_network_type_2       --第二责任网点网点类型(基础资料)
              , t6.provider_id                                                 as duty_provider_id_2        --第二责责任网点省份ID(基础资料)
              , t6.provider_desc                                               as duty_provider_desc_2      --第二责责任网点省份(基础资料)
              , t6.city_id                                                     as duty_city_id_2            --第二责责任网点城市ID(基础资料)
              , t6.city_desc                                                   as duty_city_desc_2          --第二责责任网点城市(基础资料)
              , t6.area_id                                                     as duty_area_id_2            --第二责责任网点区/县id
              , t6.area_desc                                                   as duty_area_desc_2          --第二责责任网点区/县
              , t6.is_enable                                                   as duty_is_enable_2          --第二责责任网点状态 0待启用,1启用,2禁用
              , t6.is_suspend                                                  as duty_is_suspend_2         --第二责责任网点是否暂停网点(1是,2否)
              , t6.zone_id                                                     as transfer_district_id_2    --第二责任网点片区id
              , t6.zone_code                                                   as transfer_district_code_2  --第二责任网点片区code
              , t6.zone_name                                                   as transfer_district_desc_2  --第二责任网点片区desc
              , nvl(t6.virt_code, t6.agent_code)                               as duty_virt_code_2          --第二责任网点责任网点虚拟代理区code
              , nvl(t6.virt_name, t6.agent_name)                               as duty_virt_name_2          --第二责任网点责任网点虚拟代理区name
              , t6.is_virtual_agent                                            as duty_is_virtual_agent_2   --第二责任网点是否是虚拟代理区 1是
              , t7.manage_code                                                 as duty_manage_code_3        --第三责任网点管理大区名称(基础资料)
              , t7.manage_name                                                 as duty_manage_name_3        --第三责任网点管理大区名称(基础资料)
              , t7.regional_id                                                 as duty_regional_id_3        --第三责任网点大区ID(基础资料)
              , t7.regional_desc                                               as duty_regional_desc_3      --第三责任网点大区name(基础资料)
              , if(t7.name = '承运商', t7.financial_center_code, t7.agent_code)   as duty_agent_code_3         --第三责任网点代理区编码(基础资料)
              , if(t7.name = '承运商', t7.financial_center_desc, t7.agent_name)   as duty_agent_name_3         --第三责任网点代理区编码(基础资料)
              , t7.network_type                                                as duty_network_type_3       --第三责任网点类型(基础资料)
              , t7.provider_id                                                 as duty_provider_id_3        --第三责任网点省份ID(基础资料)
              , t7.provider_desc                                               as duty_provider_desc_3      --第三责任网点省份(基础资料)
              , t7.city_id                                                     as duty_city_id_3            --第三责任网点城市ID(基础资料)
              , t7.city_desc                                                   as duty_city_desc_3          --第三责任网点城市(基础资料)
              , t7.area_id                                                     as duty_area_id_3            --第三责任网点区/县id
              , t7.area_desc                                                   as duty_area_desc_3          --第三责任网点区/县
              , t7.is_enable                                                   as duty_is_enable_3          --第三责任网点状态 0待启用,1启用,2禁用
              , t7.is_suspend                                                  as duty_is_suspend_3         --第三责任网点是否暂停网点(1是,2否)
              , t7.zone_id                                                     as transfer_district_id_3    --第三责任网点片区id
              , t7.zone_code                                                   as transfer_district_code_3  --第三责任网点片区code
              , t7.zone_name                                                   as transfer_district_desc_3  --第三责任网点片区desc
              , nvl(t7.virt_code, t7.agent_code)                               as duty_virt_code_3          --第三责任网点责任网点虚拟代理区code
              , nvl(t7.virt_name, t7.agent_name)                               as duty_virt_name_3          --第三责任网点责任网点虚拟代理区name
              , t7.is_virtual_agent                                            as duty_is_virtual_agent_3   --第三责任网点是否是虚拟代理区 1是
              , t8.manage_code                                                 as duty_manage_code_4        --第四责任网点管理大区名称(基础资料)
              , t8.manage_name                                                 as duty_manage_name_4        --第四责任网点管理大区名称(基础资料)
              , t8.regional_id                                                 as duty_regional_id_4        --第四责任网点大区ID(基础资料)
              , t8.regional_desc                                               as duty_regional_desc_4      --第四责任网点大区name(基础资料)
              , if(t8.name = '承运商', t8.financial_center_code, t8.agent_code)   as duty_agent_code_4         --第四责任网点代理区编码(基础资料)
              , if(t8.name = '承运商', t8.financial_center_desc, t8.agent_name)   as duty_agent_name_4         --第四责任网点代理区编码(基础资料)
              , t8.network_type                                                as duty_network_type_4       --第四责任网点类型(基础资料)
              , t8.provider_id                                                 as duty_provider_id_4        --第四责责任网点省份ID(基础资料)
              , t8.provider_desc                                               as duty_provider_desc_4      --第四责责任网点省份(基础资料)
              , t8.city_id                                                     as duty_city_id_4            --第四责责任网点城市ID(基础资料)
              , t8.city_desc                                                   as duty_city_desc_4          --第四责责任网点城市(基础资料)
              , t8.area_id                                                     as duty_area_id_4            --第四责责任网点区/县id
              , t8.area_desc                                                   as duty_area_desc_4          --第四责责任网点区/县
              , t8.is_enable                                                   as duty_is_enable_4          --第四责责任网点状态 0待启用,1启用,2禁用
              , t8.is_suspend                                                  as duty_is_suspend_4         --第四责责任网点是否暂停网点(1是,2否)
              , t8.zone_id                                                     as transfer_district_id_4    --第四责任网点片区id
              , t8.zone_code                                                   as transfer_district_code_4  --第四责任网点片区code
              , t8.zone_name                                                   as transfer_district_desc_4  --第四责任网点片区desc
              , nvl(t8.virt_code, t8.agent_code)                               as duty_virt_code_4          --第四责任网点责任网点虚拟代理区code
              , nvl(t8.virt_name, t8.agent_name)                               as duty_virt_name_4          --第四责任网点责任网点虚拟代理区name
              , t8.is_virtual_agent                                            as duty_is_virtual_agent_4   --第四责任网点是否是虚拟代理区 1是
              , t9.manage_code                                                 as duty_manage_code_5        --第五责任网点管理大区名称(基础资料)
              , t9.manage_name                                                 as duty_manage_name_5        --第五责任网点管理大区名称(基础资料)
              , t9.regional_id                                                 as duty_regional_id_5        --第五责任网点大区ID(基础资料)
              , t9.regional_desc                                               as duty_regional_desc_5      --第五责任网点大区name(基础资料)
              , if(t9.name = '承运商', t9.financial_center_code, t9.agent_code)   as duty_agent_code_5         --第五责任网点代理区编码(基础资料)
              , if(t9.name = '承运商', t9.financial_center_desc, t9.agent_name)   as duty_agent_name_5         --第五责任网点代理区编码(基础资料)
              , t9.network_type                                                as duty_network_type_5       --第五责任网点类型(基础资料)
              , t9.provider_id                                                 as duty_provider_id_5        --第五责责任网点省份ID(基础资料)
              , t9.provider_desc                                               as duty_provider_desc_5      --第五责责任网点省份(基础资料)
              , t9.city_id                                                     as duty_city_id_5            --第五责责任网点城市ID(基础资料)
              , t9.city_desc                                                   as duty_city_desc_5          --第五责责任网点城市(基础资料)
              , t9.area_id                                                     as duty_area_id_5            --第五责责任网点区/县id
              , t9.area_desc                                                   as duty_area_desc_5          --第五责责任网点区/县
              , t9.is_enable                                                   as duty_is_enable_5          --第五责责任网点状态 0待启用,1启用,2禁用
              , t9.is_suspend                                                  as duty_is_suspend_5         --第五责责任网点是否暂停网点(1是,2否)
              , t9.zone_id                                                     as transfer_district_id_5    --第五责任网点片区id
              , t9.zone_code                                                   as transfer_district_code_5  --第五责任网点片区code
              , t9.zone_name                                                   as transfer_district_desc_5  --第五责任网点片区desc
              , nvl(t9.virt_code, t9.agent_code)                               as duty_virt_code_5          --第五责任网点责任网点虚拟代理区code
              , nvl(t9.virt_name, t9.agent_name)                               as duty_virt_name_5          --第五责任网点责任网点虚拟代理区name
              , t9.is_virtual_agent                                            as duty_is_virtual_agent_5   --第五责任网点是否是虚拟代理区 1是
              , t1.network_cnt                                                 as network_cnt               --责任网点数
              , t1.damage_eliminate
              , if(t1.work_type = '线上理赔' and t10.waybill_no is not null, 1, 0) as arbitration_delete        --1.质量工单且满足仲裁条件,需剔除
              , nvl(t3.virt_code, t3.agent_code)                               as sb_virt_code              --申报虚拟代理区code(基础资料)
              , nvl(t3.virt_name, t3.agent_name)                               as sb_virt_name              --申报虚拟代理区name(基础资料)
              , t3.zone_id                                                     as sb_zone_id                --申报片区id(基础资料)
              , t3.zone_code                                                   as sb_zone_code              --申报片区code(基础资料)
              , t3.zone_name                                                   as sb_zone_name              --申报片区name(基础资料)
              , t3.agent_code                                                  as sb_agent_code             --申报代理区code(基础资料)
              , t3.agent_name                                                  as sb_agent_name             --申报代理区name(基础资料)
              , t1.cost_center                                                 as cost_center               --成本中心
              , t1.id                                                          as id
              , t1.zcgoods_value                                               as zcgoods_value             --仲裁自带的物品价值
              , responsibility_reply_status                                                                 --责任方回复状态：1、未回复 2、已回复
              , responsibility_confirm_status                                                               --责任方认责状态：1、未认责 2、已认责
              , overtime_flag                                                                               --超时未处理标识1超时,0不超时
              , warn_worn_flag                                                                              --破损,预警破损标识1代表破损,0代表预警
              , high_worn_flag                                                                              --高破损客户1代表搞破损
              , finish_time_remark
              , create_time_remark
              , t2.area_code                                                   as duty_area_code
              , t2.area_name                                                   as duty_area_name
              , t6.area_code                                                   as duty_area_code_2
              , t6.area_name                                                   as duty_area_name_2
              , t7.area_code                                                   as duty_area_code_3
              , t7.area_name                                                   as duty_area_name_3
              , t8.area_code                                                   as duty_area_code_4
              , t8.area_name                                                   as duty_area_name_4
              , t9.area_code                                                   as duty_area_code_5
              , t9.area_name                                                   as duty_area_name_5
              , substr(t1.finish_time, 1, 10)                                  as date_time                 --日期
         from union_all_table t1
                  join network_massage t2
                       on nvl(t1.resp_network_code,'-999') = t2.code
                  left join network_massage t3
                            on nvl(t1.create_network_code,'-999') = t3.code
                  left join network_massage t6
                            on nvl(t1.resp_network_code_2,'-999') = t6.code
                  left join network_massage t7
                            on nvl(t1.resp_network_code_3,'-999') = t7.code
                  left join network_massage t8
                            on nvl(t1.resp_network_code_4,'-999') = t8.code
                  left join network_massage t9
                            on nvl(t1.resp_network_code_5,'-999') = t9.code
                  left join arbitration_delete t10
                            on t1.waybill_no = t10.waybill_no
     )
insert
overwrite
table
jms_dm.dm_sqs_worn_waybill_detail_mid_dt
partition
(
dt
)
--      result_table2 as (
select t1.computer_flag                                                                                    --1代表破损,2代表预警,3代表高危破损
     , t1.work_type                                                       as work_type                     --工单类型
     , t1.waybill_no                                                      as waybill_no                    --运单号
     , t1.work_order_code                                                 as work_order_code               --理赔编号
     , t1.finish_time                                                     as finish_time                   --提交时间
     , t1.submit_status                                                   as submit_status                 --状态
     , t1.is_carrier                                                      as is_carrier                    --是否承运商
     , t1.source                                                          as source                        --只要1-客服
     , t1.resp_network_id                                                 as resp_network_id               --责任网点id
     , t1.resp_network_code                                               as resp_network_code             --责任网点code
     , t1.resp_network_name                                               as resp_network_name             --责任网点name
     , t1.resp_network_id_2                                               as resp_network_id_2             --责任网点id2
     , t1.resp_network_code_2                                             as resp_network_code_2           --责任网点code2
     , t1.resp_network_name_2                                             as resp_network_name_2           --责任网点name2
     , t1.resp_network_id_3                                               as resp_network_id_3             --责任网点id3
     , t1.resp_network_code_3                                             as resp_network_code_3           --责任网点code3
     , t1.resp_network_name_3                                             as resp_network_name_3           --责任网点name3
     , t1.resp_network_id_4                                               as resp_network_id_4             --责任网点id4
     , t1.resp_network_code_4                                             as resp_network_code_4           --责任网点code4
     , t1.resp_network_name_4                                             as resp_network_name_4           --责任网点name4
     , t1.resp_network_id_5                                               as resp_network_id_5             --责任网点id5
     , t1.resp_network_code_5                                             as resp_network_code_5           --责任网点code5
     , t1.resp_network_name_5                                             as resp_network_name_5           --责任网点name5
     , t1.resp_proxy_id                                                   as resp_proxy_id                 --责任代理区id
     , t1.resp_proxy_code                                                 as resp_proxy_code               --责任代理区code
     , t1.resp_proxy_name                                                 as resp_proxy_name               --责任代理区name
     , t1.resp_proxy_id_2                                                 as resp_proxy_id_2               --责任代理区id2
     , t1.resp_proxy_code_2                                               as resp_proxy_code_2             --责任代理区code2
     , t1.resp_proxy_name_2                                               as resp_proxy_name_2             --责任代理区name2
     , t1.resp_proxy_id_3                                                 as resp_proxy_id_3               --责任代理区id_3
     , t1.resp_proxy_code_3                                               as resp_proxy_code_3             --责任代理区code_3
     , t1.resp_proxy_name_3                                               as resp_proxy_name_3             --责任代理区name_3
     , t1.resp_proxy_id_4                                                 as resp_proxy_id_4               --责任代理区id_4
     , t1.resp_proxy_code_4                                               as resp_proxy_code_4             --责任代理区code_4
     , t1.resp_proxy_name_4                                               as resp_proxy_name_4             --责任代理区name_4
     , t1.resp_proxy_id_5                                                 as resp_proxy_id_5               --责任代理区id_5
     , t1.resp_proxy_code_5                                               as resp_proxy_code_5             --责任代理区code_5
     , t1.resp_proxy_name_5                                               as resp_proxy_name_5             --责任代理区name_5
     , t1.create_network_id                                               as create_network_id             --申报网点id
     , t1.create_network_code                                             as create_network_code           --申报网点code
     , t1.create_network_name                                             as create_network_name           --申报网点name
     , t1.create_parent_id                                                as create_parent_id              --申报代理区id
     , t1.create_parent_code                                              as create_parent_code            --申报代理区code
     , t1.create_parent_name                                              as create_parent_name            --申报代理区name
     , t1.problem_type_id                                                 as problem_type_id               --只要 1-遗失
     , t1.problem_type_code                                               as problem_type_code             --一级类型code
     , t1.problem_type_name                                               as problem_type_name             --一级类型name
     , t1.problem_type_subject_id                                         as problem_type_subject_id       --二级类型id
     , t1.problem_type_subject_code                                       as problem_type_subject_code     --二级类型code
     , t1.problem_type_subject_name                                       as problem_type_subject_name     --二级类型name
     , t1.goods_value                                                     as goods_value                   --金额1
     , if(t1.work_type = '仲裁' and t1.network_cnt < 2, 0, t1.goods_value2) as goods_value2                  --金额2
     , if(t1.work_type = '仲裁' and t1.network_cnt < 3, 0, t1.goods_value3) as goods_value3                  --金额3
     , if(t1.work_type = '仲裁' and t1.network_cnt < 4, 0, t1.goods_value4) as goods_value4                  --金额4
     , if(t1.work_type = '仲裁' and t1.network_cnt < 5, 0, t1.goods_value5) as goods_value5                  --金额5
     , t1.arb_create_time                                                 as arb_create_time               --仲裁申报时间
     , t1.delay_day                                                       as delay_day                     --延误天数
     , t1.status                                                          as status                        --异常说明
     , t1.arrival_time                                                    as arrival_time                  --责任网点到件时间
     , nvl(t1.collect_time, t5.collect_time)                              as collect_time                  --揽收时间
     , t1.handling_fee                                                    as handling_fee                  --手续费
     , t1.valid_status                                                    as valid_status                  --生效状态 1:生效 2:失效
     , t1.rn                                                              as rn                            --每种工单的去重row_number()
     , t1.flag                                                            as flag                          --仲裁1,线上理赔2,质量工单3
     , t1.finish_type                                                     as finish_type                   --完结类型
     , t1.rm                                                              as rm                            --根据运单分组,flag排序,做dense_rank()
     , t1.duty_network_type                                               as duty_network_type             --责任网点网点类型(基础资料)
     , nvl(t1.duty_virt_code, t1.duty_agent_code)                         as duty_virt_code                --责任网点虚拟代理区code(基础资料)
     , nvl(t1.duty_virt_name, t1.duty_agent_name)                         as duty_virt_name                --责任网点虚拟代理区name(基础资料)
     , t1.duty_is_virtual_agent                                           as duty_is_virtual_agent         --责任网点是否是虚拟代理区 1是
     , t1.duty_manage_code                                                as duty_manage_code              --责任网点管理大区名称(基础资料)
     , t1.duty_manage_name                                                as duty_manage_name              --责任网点管理大区名称(基础资料)
     , t1.duty_regional_id                                                as duty_regional_id              --责任网点大区ID(基础资料)
     , t1.duty_regional_desc                                              as duty_regional_desc            --责任网点大区name(基础资料)
     , t1.duty_agent_code                                                 as duty_agent_code               --责任网点代理区编码(基础资料)
     , t1.duty_agent_name                                                 as duty_agent_name               --责任网点代理区名称(基础资料)
     , t1.duty_fran_code                                                  as duty_fran_code                --责任网点加盟商编码(基础资料)
     , t1.duty_fran_name                                                  as duty_fran_name                --责任网点加盟商名称(基础资料)
     , t1.duty_center_code                                                as duty_center_code              --责任网点中心编码(基础资料)
     , t1.duty_center_name                                                as duty_center_name              --责任网点中心名称(基础资料)
     , t1.duty_provider_id                                                as duty_provider_id              --责任网点省份ID(基础资料)
     , t1.duty_provider_desc                                              as duty_provider_desc            --责任网点省份(基础资料)
     , t1.duty_city_id                                                    as duty_city_id                  --责任网点城市ID(基础资料)
     , t1.duty_city_desc                                                  as duty_city_desc                --责任网点城市(基础资料)
     , t1.duty_area_id                                                    as duty_area_id                  --责任网点区/县id
     , t1.duty_area_desc                                                  as duty_area_desc                --责任网点区/县
     , t1.duty_is_enable                                                  as duty_is_enable                --责任网点状态 0待启用,1启用,2禁用
     , t1.duty_is_suspend                                                 as duty_is_suspend               --责任网点是否暂停网点(1是,2否)
     , t1.sb_manage_code                                                  as sb_manage_code                --申报网点管理大区名称(基础资料)
     , t1.sb_manage_name                                                  as sb_manage_name                --申报网点管理大区名称(基础资料)
     , t1.sb_regional_id                                                  as sb_regional_id                --申报网点大区ID(基础资料)
     , t1.sb_regional_desc                                                as sb_regional_desc              --申报网点大区name(基础资料)
     , t1.sb_network_type                                                 as sb_network_type               --申报网点网点类型(基础资料)
     , t1.transfer_district_id                                            as transfer_district_id          --责任网点片区id
     , t1.transfer_district_code                                          as transfer_district_code        --责任网点片区code
     , t1.transfer_district_desc                                          as transfer_district_desc        --责任网点片区desc
     , t1.is_delete                                                       as is_delete                     --1代表需要的数据未剔除0是不要的
--               , substr(t1.finish_time, 1, 10)                                      as date_time                     --日期
     , t5.customer_code                                                   as customer_code                 --客户编码
     , t5.customer_name                                                   as customer_name                 --客户名字
     , null                                                               as remark3                       --备用
     , t1.duty_manage_code_2                                              as duty_manage_code_2            --第二责任网点管理大区名称(基础资料)
     , t1.duty_manage_name_2                                              as duty_manage_name_2            --第二责任网点管理大区名称(基础资料)
     , t1.duty_regional_id_2                                              as duty_regional_id_2            --第二责任网点大区ID(基础资料)
     , t1.duty_regional_desc_2                                            as duty_regional_desc_2          --第二责任网点大区name(基础资料)
     , t1.duty_agent_code_2                                               as duty_agent_code_2             --第二责任网点代理区编码(基础资料)
     , t1.duty_agent_name_2                                               as duty_agent_name_2             --第二责任网点代理区编码(基础资料)
     , t1.duty_network_type_2                                             as duty_network_type_2           --第二责任网点网点类型(基础资料)
     , t1.duty_provider_id_2                                              as duty_provider_id_2            --第二责责任网点省份ID(基础资料)
     , t1.duty_provider_desc_2                                            as duty_provider_desc_2          --第二责责任网点省份(基础资料)
     , t1.duty_city_id_2                                                  as duty_city_id_2                --第二责责任网点城市ID(基础资料)
     , t1.duty_city_desc_2                                                as duty_city_desc_2              --第二责责任网点城市(基础资料)
     , t1.duty_area_id_2                                                  as duty_area_id_2                --第二责责任网点区/县id
     , t1.duty_area_desc_2                                                as duty_area_desc_2              --第二责责任网点区/县
     , t1.duty_is_enable_2                                                as duty_is_enable_2              --第二责责任网点状态 0待启用,1启用,2禁用
     , t1.duty_is_suspend_2                                               as duty_is_suspend_2             --第二责责任网点是否暂停网点(1是,2否)
     , t1.transfer_district_id_2                                          as transfer_district_id_2        --第二责责任网点片区id
     , t1.transfer_district_code_2                                        as transfer_district_code_2      --第二责责任网点片区code
     , t1.transfer_district_desc_2                                        as transfer_district_desc_2      --第二责责任网点片区desc
     , nvl(t1.duty_virt_code_2, t1.duty_agent_code_2)                     as duty_virt_code_2              --第二责任网点责任网点虚拟代理区code
     , nvl(t1.duty_virt_name_2, t1.duty_agent_name_2)                     as duty_virt_name_2              --第二责任网点责任网点虚拟代理区name
     , t1.duty_is_virtual_agent_2                                         as duty_is_virtual_agent_2       --第二责任网点是否是虚拟代理区 1是
     , t1.duty_manage_code_3                                              as duty_manage_code_3            --第三责任网点管理大区名称(基础资料)
     , t1.duty_manage_name_3                                              as duty_manage_name_3            --第三责任网点管理大区名称(基础资料)
     , t1.duty_regional_id_3                                              as duty_regional_id_3            --第三责任网点大区ID(基础资料)
     , t1.duty_regional_desc_3                                            as duty_regional_desc_3          --第三责任网点大区name(基础资料)
     , t1.duty_agent_code_3                                               as duty_agent_code_3             --第三责任网点代理区编码(基础资料)
     , t1.duty_agent_name_3                                               as duty_agent_name_3             --第三责任网点代理区编码(基础资料)
     , t1.duty_network_type_3                                             as duty_network_type_3           --第三责任网点类型(基础资料)
     , t1.duty_provider_id_3                                              as duty_provider_id_3            --第三责任网点省份ID(基础资料)
     , t1.duty_provider_desc_3                                            as duty_provider_desc_3          --第三责任网点省份(基础资料)
     , t1.duty_city_id_3                                                  as duty_city_id_3                --第三责任网点城市ID(基础资料)
     , t1.duty_city_desc_3                                                as duty_city_desc_3              --第三责任网点城市(基础资料)
     , t1.duty_area_id_3                                                  as duty_area_id_3                --第三责任网点区/县id
     , t1.duty_area_desc_3                                                as duty_area_desc_3              --第三责任网点区/县
     , t1.duty_is_enable_3                                                as duty_is_enable_3              --第三责任网点状态 0待启用,1启用,2禁用
     , t1.duty_is_suspend_3                                               as duty_is_suspend_3             --第三责任网点是否暂停网点(1是,2否)
     , t1.transfer_district_id_3                                          as transfer_district_id_3        --第三责任网点片区id
     , t1.transfer_district_code_3                                        as transfer_district_code_3      --第三责任网点片区code
     , t1.transfer_district_desc_3                                        as transfer_district_desc_3      --第三责任网点片区desc
     , nvl(t1.duty_virt_code_3, t1.duty_agent_code_3)                     as duty_virt_code_3              --第三责任网点责任网点虚拟代理区code
     , nvl(t1.duty_virt_name_3, t1.duty_agent_name_3)                     as duty_virt_name_3              --第三责任网点责任网点虚拟代理区name
     , t1.duty_is_virtual_agent_3                                         as duty_is_virtual_agent_3       --第三责任网点是否是虚拟代理区 1是
     , t1.duty_manage_code_4                                              as duty_manage_code_4            --第四责任网点管理大区名称(基础资料)
     , t1.duty_manage_name_4                                              as duty_manage_name_4            --第四责任网点管理大区名称(基础资料)
     , t1.duty_regional_id_4                                              as duty_regional_id_4            --第四责任网点大区ID(基础资料)
     , t1.duty_regional_desc_4                                            as duty_regional_desc_4          --第四责任网点大区name(基础资料)
     , t1.duty_agent_code_4                                               as duty_agent_code_4             --第四责任网点代理区编码(基础资料)
     , t1.duty_agent_name_4                                               as duty_agent_name_4             --第四责任网点代理区编码(基础资料)
     , t1.duty_network_type_4                                             as duty_network_type_4           --第四责任网点类型(基础资料)
     , t1.duty_provider_id_4                                              as duty_provider_id_4            --第四责责任网点省份ID(基础资料)
     , t1.duty_provider_desc_4                                            as duty_provider_desc_4          --第四责责任网点省份(基础资料)
     , t1.duty_city_id_4                                                  as duty_city_id_4                --第四责责任网点城市ID(基础资料)
     , t1.duty_city_desc_4                                                as duty_city_desc_4              --第四责责任网点城市(基础资料)
     , t1.duty_area_id_4                                                  as duty_area_id_4                --第四责责任网点区/县id
     , t1.duty_area_desc_4                                                as duty_area_desc_4              --第四责责任网点区/县
     , t1.duty_is_enable_4                                                as duty_is_enable_4              --第四责责任网点状态 0待启用,1启用,2禁用
     , t1.duty_is_suspend_4                                               as duty_is_suspend_4             --第四责责任网点是否暂停网点(1是,2否)
     , t1.transfer_district_id_4                                          as transfer_district_id_4        --第四责任网点片区id
     , t1.transfer_district_code_4                                        as transfer_district_code_4      --第四责任网点片区code
     , t1.transfer_district_desc_4                                        as transfer_district_desc_4      --第四责任网点片区desc
     , nvl(t1.duty_virt_code_4, t1.duty_agent_code_4)                     as duty_virt_code_4              --第四责任网点责任网点虚拟代理区code
     , nvl(t1.duty_virt_name_4, t1.duty_agent_name_4)                     as duty_virt_name_4              --第四责任网点责任网点虚拟代理区name
     , t1.duty_is_virtual_agent_4                                         as duty_is_virtual_agent_4       --第四责任网点是否是虚拟代理区 1是
     , t1.duty_manage_code_5                                              as duty_manage_code_5            --第五责任网点管理大区名称(基础资料)
     , t1.duty_manage_name_5                                              as duty_manage_name_5            --第五责任网点管理大区名称(基础资料)
     , t1.duty_regional_id_5                                              as duty_regional_id_5            --第五责任网点大区ID(基础资料)
     , t1.duty_regional_desc_5                                            as duty_regional_desc_5          --第五责任网点大区name(基础资料)
     , t1.duty_agent_code_5                                               as duty_agent_code_5             --第五责任网点代理区编码(基础资料)
     , t1.duty_agent_name_5                                               as duty_agent_name_5             --第五责任网点代理区编码(基础资料)
     , t1.duty_network_type_5                                             as duty_network_type_5           --第五责任网点类型(基础资料)
     , t1.duty_provider_id_5                                              as duty_provider_id_5            --第五责责任网点省份ID(基础资料)
     , t1.duty_provider_desc_5                                            as duty_provider_desc_5          --第五责责任网点省份(基础资料)
     , t1.duty_city_id_5                                                  as duty_city_id_5                --第五责责任网点城市ID(基础资料)
     , t1.duty_city_desc_5                                                as duty_city_desc_5              --第五责责任网点城市(基础资料)
     , t1.duty_area_id_5                                                  as duty_area_id_5                --第五责责任网点区/县id
     , t1.duty_area_desc_5                                                as duty_area_desc_5              --第五责责任网点区/县
     , t1.duty_is_enable_5                                                as duty_is_enable_5              --第五责责任网点状态 0待启用,1启用,2禁用
     , t1.duty_is_suspend_5                                               as duty_is_suspend_5             --第五责责任网点是否暂停网点(1是,2否)
     , t1.transfer_district_id_5                                          as transfer_district_id_5        --第五责任网点片区id
     , t1.transfer_district_code_5                                        as transfer_district_code_5      --第五责任网点片区code
     , t1.transfer_district_desc_5                                        as transfer_district_desc_5      --第五责任网点片区desc
     , nvl(t1.duty_virt_code_5, t1.duty_agent_code_5)                     as duty_virt_code_5              --第五责任网点责任网点虚拟代理区code
     , nvl(t1.duty_virt_name_5, t1.duty_agent_name_5)                     as duty_virt_name_5              --第五责任网点责任网点虚拟代理区name
     , t1.duty_is_virtual_agent_5                                         as duty_is_virtual_agent_5       --第五责任网点是否是虚拟代理区 1是
     , t5.pick_network_code                                               as pick_network_code             --寄件网点code
     , t5.pick_network_name                                               as pick_network_name             --寄件网点
     , t5.order_source_code                                               as order_source_code             --订单来源code
     , t5.ordersource_name                                                as ordersource_name              --订单来源
     , t1.network_cnt                                                     as network_cnt                   --责任网点数
     , t1.damage_eliminate                                                as damage_eliminate              --破损剔除标志 1：是 2 否
     , t5.dispatch_network_code                                           as dispatch_network_code         --派件code
     , t5.dispatch_network_name                                           as dispatch_network_name         --派件name
     , t5.sender_province_id                                              as sender_province_id            --寄件省份id
     , t5.sender_province_name                                            as sender_province_name          --寄件省份name
     , t5.sender_city_id                                                  as sender_city_id                --寄件城市id
     , t5.sender_city_name                                                as sender_city_name              --寄件城市id
     , t1.sb_virt_code                                                    as sb_virt_code                  --申报虚拟代理区code(基础资料)
     , t1.sb_virt_name                                                    as sb_virt_name                  --申报虚拟代理区name(基础资料)
     , t1.sb_zone_id                                                      as sb_zone_id                    --申报片区id(基础资料)
     , t1.sb_zone_code                                                    as sb_zone_code                  --申报片区code(基础资料)
     , t1.sb_zone_name                                                    as sb_zone_name                  --申报片区name(基础资料)
     , t1.sb_agent_code                                                   as sb_agent_code                 --申报代理区code(基础资料)
     , t1.sb_agent_name                                                   as sb_agent_name                 --申报代理区name(基础资料)
     , t1.cost_center                                                     as cost_center                   --成本中心
     , t1.id                                                              as id                            --工单ID
     , t1.zcgoods_value                                                   as zcgoods_value                 --仲裁自带的物品价值
     , t5.goods_type_code                                                 as goods_type_code               --物品类型code
     , t5.goods_type_name                                                 as goods_type_name               --物品类型名称
     , t1.responsibility_reply_status                                     as responsibility_reply_status   --责任方回复状态：1、未回复 2、已回复
     , t1.responsibility_confirm_status                                   as responsibility_confirm_status --责任方认责状态：1、未认责 2、已认责
     , t1.overtime_flag                                                   as overtime_flag                 --超时未处理标识1超时,0不超时
     , t1.warn_worn_flag                                                  as warn_worn_flag                --破损,预警破损标识1代表破损,0代表预警
     , t1.high_worn_flag                                                  as high_worn_flag                --高破损客户1代表搞破损
     , if(t6.virt_code is null, t6.agent_code, virt_code)                 as pick_virt_code                --寄件网点虚拟代理区code
     , if(t6.virt_name is null, t6.agent_name, virt_name)                 as pick_virt_name                --寄件网点虚拟代理区name
     , t6.is_virtual_agent                                                as pick_is_virtual_agent         --寄件网点是否是虚拟代理区
     , t6.agent_code                                                      as pick_agent_code               --寄件网点代理区code
     , t6.agent_name                                                      as pick_agent_name               --寄件网点代理区name
     , t6.zone_id                                                         as pick_district_id              --寄件网点片区id
     , t6.zone_code                                                       as pick_district_code            --寄件网点片区code
     , t6.zone_name                                                       as pick_district_desc            --寄件网点片区name
     , t6.network_type                                                    as pick_network_type             --寄件网点网点类型
     , t5.goods_name                                                      as goods_name                    --物品名称
     , t5.express_type_code                                               as express_type_code             --产品类型code
     , t5.express_type_name                                               as express_type_name             --产品类型名称
     , t1.finish_time_remark                                              as finish_time_remark            --完结时间
     , t1.create_time_remark                                              as create_time_remark            --创建时间
     , date_time                                                          as date_time                     --日期
     , t1.duty_area_code
     , t1.duty_area_name
     , t1.duty_area_code_2
     , t1.duty_area_name_2
     , t1.duty_area_code_3
     , t1.duty_area_name_3
     , t1.duty_area_code_4
     , t1.duty_area_name_4
     , t1.duty_area_code_5
     , t1.duty_area_name_5
     , date_time                                                          as dt
from result_table1 t1
         left join waybill_detail t5
                   on t1.waybill_no = t5.waybill_no
         left join network_massage t6
                   on t5.pick_network_code = t6.code
where t1.date_time <= '{{ execution_date | cst_ds }}'
  and t1.date_time >= date_add('{{ execution_date | cst_ds }}', -89)
  and t1.arbitration_delete = 0
    distribute by dt;